﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;

namespace ConfigHelper.Common
{
    public class Common
    {
        
        #region 显示
        public static string GetList<T>(string TableName, params SqlParameter[] Parameter)
        {
            string sql = "";
            if (Parameter.Length != 0)
            {
                sql = "select * from {0} where 1=1 {1}";
                string where = "";
                Type type = typeof(T);
                foreach (var item in type.GetProperties())
                {
                    foreach (var p in Parameter)
                    {
                        if ('@' + item.Name == p.ParameterName)
                        {
                            if (item.PropertyType == typeof(string))
                            {
                                where += " and " + item.Name + " like'%" + p.Value + "%'";
                            }
                            else
                            {
                                where += " and " + item.Name + "=" + p.Value;
                            }
                        }
                    }
                }
                return string.Format(sql, TableName, where);
            }
            else
            {
                sql = "select * from {0} ";
                return string.Format(sql, TableName);
            }
        }
        #endregion

        #region 添加
        public static string AddSQL<T>(T t, string tablename, Dictionary<string, bool> PKName) where T : class
        {
            string SQL = "insert into {0} ({1}) values ({2})";

            string filed = "";
            string value = "";

            string PK = PKName.Keys.First();
            bool ifPK = PKName.Values.First();

            foreach (var item in t.GetType().GetProperties())
            {
                if (ifPK == true)
                {
                    if (item.Name == PK)
                    {
                        continue;
                    }
                }
                filed += item.Name + ',';
                if (item.PropertyType == typeof(string) || item.PropertyType == typeof(DateTime) || item.PropertyType == typeof(Guid))
                {
                    value += "'" + item.GetValue(t) + "',";
                }
                if (item.PropertyType == typeof(int) || item.PropertyType == typeof(decimal))
                {
                    value += item.GetValue(t) + ",";
                }
                else if (item.PropertyType == typeof(float))
                {
                    value += "'" + item.GetValue(t) + "',";
                }
            }
            filed = filed.TrimEnd(',');
            value = value.TrimEnd(',');
            return string.Format(SQL, tablename, filed, value);
        }
        #endregion

        #region 删除
        public static string DeleteSQL<T>(T t, string tablename, Dictionary<string, bool> PKName) where T : class
        {
            string sql = "delete from {0} where {1} ";
            string pk = PKName.Keys.First();
            string where = "";
            var pkitem = t.GetType().GetProperty(pk);
            if (pkitem.PropertyType == typeof(string) || pkitem.PropertyType == typeof(DateTime))
            {
                where = pk + "='" + pkitem.GetValue(t) + "'";
            }
            else
            {
                where = pk + "=" + pkitem.GetValue(t);
            }
            return string.Format(sql, tablename, where);
        }
        #endregion

        #region 修改
        public static string UpdateSQL<T>(T t, string tablename, Dictionary<string, bool> PKName) where T : class
        {
            string sql = "update {0} set {1} where {2}";

            string value = "";
            string where = "";

            string PK = PKName.Keys.First();
            bool ifPK = PKName.Values.First();

            foreach (var item in t.GetType().GetProperties())
            {
                if (ifPK)
                {

                    if (item.Name == PK)
                    {
                        //string id = item.GetValue(t).ToString();
                        //PK += "=" + id;
                        continue;
                    }
                }
                if (item.PropertyType == typeof(string) || item.PropertyType == typeof(DateTime))
                {
                    value += item.Name + "='" + item.GetValue(t) + "',";
                }
                else if (item.PropertyType == typeof(int) || item.PropertyType == typeof(decimal))
                {
                    value += item.Name + "=" + item.GetValue(t) + ",";
                }
            }

            value = value.TrimEnd(',');

            var pkitem = t.GetType().GetProperty(PK);
            if (pkitem.PropertyType == typeof(string) || pkitem.PropertyType == typeof(DateTime))
            {
                where = PK + "='" + pkitem.GetValue(t) + "'";
            }
            else
            {
                where = PK + "=" + pkitem.GetValue(t);
            }

            return string.Format(sql, tablename, value, where);

        }
        #endregion

        #region 连接查询
        public static string GetNewList<T>(params SqlParameter[] Parameter)
        {
            string Sql = "";
            if (Parameter.Length != 0)
            {
                Sql = @"select student.StudentId,student.UserId,student.StudentName,users.UserName,student.Sex,
                        student.Age,student.Email,student.PhoneNumber,d.Name as College,d1.Name as Professional,
                        class.ClassName,teacher.TeacherName,student.CreateTime
                        from [Student] student join [Users] users on student.UserId=users.Id  join  Dictionary 
                        d on student.CollegeId=d.Id join Dictionary d1 on student.ProfessionalId=d1.Id join
                        [Classes] class on student.ClassId=class.ClassId join [Teacher] teacher on class.TeacherId=
                        teacher.TeacherId";

                string where = "";
                Type type = typeof(T);
                foreach (var item in type.GetProperties())
                {
                    foreach (var p in Parameter)
                    {
                        if ('@' + item.Name == p.ParameterName)
                        {
                            if (item.PropertyType == typeof(string))
                            {
                                where += " and " + item.Name + " like'%" + p.Value + "%'";
                            }
                            else
                            {
                                where += " and " + item.Name + "=" + p.Value;
                            }
                        }
                    }
                }
                return string.Format(Sql, where);

            }
            else
            {
                Sql = @"select student.StudentId,student.UserId,student.StudentName,users.UserName,student.Sex,
                        student.Age,student.Email,student.PhoneNumber,d.Name as College,d1.Name as Professional,
                        class.ClassName,teacher.TeacherName,student.CreateTime
                        from [Student] student join [Users] users on student.UserId=users.Id  join  Dictionary 
                        d on student.CollegeId=d.Id join Dictionary d1 on student.ProfessionalId=d1.Id join
                        [Classes] class on student.ClassId=class.ClassId join [Teacher] teacher on class.TeacherId=
                        teacher.TeacherId";

                return Sql;
            }
        }
        #endregion


    }
}
